package org.diy.traffic.sources;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.HashMap;
import java.util.List;
import java.util.Locale;
import java.util.TimeZone;
import java.util.logging.Level;

import org.diy.traffic.sources.TrafficSource.TrafficSourceException;
import org.diy.traffic.updates.TrafficUpdate;
import org.diy.traffic.util.TLog;

public class SickStats extends TrafficSource {

	Connection dbConn = null;
	
	private static String sourceType = "SICKSTATS_SOURCE";

	
	private static String mysqlDbName;
	private static String mysqlUsername;
	private static String mysqlPassword;

	//don't use this!
	public SickStats(String sourceName) {
		super(sourceName);
		// TODO Auto-generated constructor stub
	}

	public SickStats(int updateInterval) throws TrafficSourceException {
		this(sourceType, updateInterval); 
	}
	
	public SickStats(String sourceName, int updateFrequency) throws TrafficSourceException {
		super(sourceName, updateFrequency);
	
		try {
			dbConn = DriverManager.getConnection(
					String.format("jdbc:mysql://localhost/%s?user=%s&password=%s", "sickcity_production", 
							"root", "")
					);
		} catch (SQLException e) {
			TLog.log(Level.SEVERE, e);
			throw new TrafficSourceException("Can't establish connection to the database!");
		}
		
	}
	
	
	public static SickStats createFromConfigData(HashMap<String, String> configParams) throws TrafficSourceException {
		mysqlDbName   = configParams.get("db");
		mysqlUsername = configParams.get("username");
		mysqlPassword = configParams.get("password");
	
		if ((mysqlDbName == null) || (mysqlUsername == null) || (mysqlPassword == null)) {
			TLog.log(Level.SEVERE, "SickStats requires a 'db', 'username', and 'password' parameter for configuration! None provided.");
			return null;
		}
		
		
		
		int use_update_interval = DEFAULT_UPDATE_FREQUENCY_S;
		
		String updateInterval = configParams.get("updateInterval");
		if (updateInterval != null) {
			use_update_interval = Integer.parseInt(updateInterval);
		} 
		
		return new SickStats(use_update_interval);
	}

	@Override
	public List<TrafficUpdate> doCheck(boolean updateLastCheck)
			throws TrafficSourceException {
		List<TrafficUpdate> update_list = new ArrayList<TrafficUpdate>();
		
		

		TLog.log(Level.FINE, "Calculating sick percentages for cities...");
	
		
		
		Statement city_stmt = null;
		ResultSet city_rs   = null;
		
		Statement update_stmt = null;
		try {
			city_stmt = dbConn.createStatement();
			city_rs   = city_stmt.executeQuery("SELECT * from cities");
			
			update_stmt = dbConn.createStatement();
			
			while (city_rs.next()) {
				int city_id = city_rs.getInt("id");
				float sick_quotient = findPercentageForCity(city_id);
				
				if (sick_quotient >= 0.0) {
				
					update_stmt.executeUpdate(
							String.format("INSERT INTO stats (city_id, quotient) VALUES (%s, %3.5f) ON DUPLICATE KEY UPDATE quotient=%3.5f",
									city_id, sick_quotient, sick_quotient)
							);
				}
				
				TLog.log(Level.FINER, 
						String.format("Got a sick percentage of %3.5f%% for city %s",
								sick_quotient, city_rs.getString("name"))
						);
			}
		
		} catch (SQLException e) {
			throw new TrafficSourceException("Error while calculating stats!");
		} finally {
			try {
				if (city_rs!=null)   city_rs.close();
				if (city_stmt!=null) city_stmt.close();
			} catch (SQLException sqle) {}
		}
		
		
		TLog.log(Level.FINE, "Completed caluclating sick percentages!");
		
		// actually returns nothing!
		return update_list;
	}
	
	private float findPercentageForCity(int cityId) throws TrafficSourceException {
		
		
		Statement mention_stmt  = null;
		ResultSet mention_rs    = null;
		
		Statement count_stmt  = null;
		ResultSet count_rs    = null;
		
		try {
			
			Calendar calendar_now = GregorianCalendar.getInstance(TimeZone.getTimeZone("GMT"));
			calendar_now.setTimeInMillis(System.currentTimeMillis());
			Date todays_date = calendar_now.getTime();
			String todays_date_str = getDateInTwitterFormat(todays_date);
			
			//String date_repr = getDateInFormat(todays_date, "E, MMMMM d");
			
			
			mention_stmt= dbConn.createStatement();
			mention_rs = mention_stmt.executeQuery(
					String.format("SELECT * FROM mentions WHERE " +
							"city_id=%s AND " +
							"DATE(mentioned_at)=STR_TO_DATE(\"%s\", '%%Y-%%m-%%d')", 
							cityId, todays_date_str)
					);
			 //select * from mentions where city_id=928874471 AND DATE(created_at)=STR_TO_DATE("2009-04-16", '');
			
			mention_rs.last();
			int sick_tweets = mention_rs.getRow();
			
			count_stmt = dbConn.createStatement();
			count_rs   = count_stmt.executeQuery(
					String.format("SELECT * FROM all_tweets WHERE " +
							"city_id=%s AND " +
							"DATE(mentioned_at)=STR_TO_DATE(\"%s\", '%%Y-%%m-%%d')",
							cityId, todays_date_str)
							);
			
			count_rs.last();
			
			int total_tweets = 1;
			if (count_rs.getRow() <= 0) {
				return (float) -1.0;
			} else  {
				total_tweets = count_rs.getRow();
				float sick_percentage = ((float)sick_tweets)/total_tweets*100;
				return sick_percentage;
			}
			
			
		} catch (SQLException e) {
			TLog.log(Level.SEVERE, e);
			throw new TrafficSourceException("Error while calculating stats for city " + cityId + ": " + e.getMessage());
		}  finally {
			try {
				if (mention_rs!=null) mention_rs.close();
				if (mention_stmt!=null) mention_stmt.close();
			
				if (count_rs!=null) count_rs.close();
				if (count_stmt!=null) count_stmt.close();
			} catch (SQLException sqle) {}
		}
	}
	
	private static String getDateInTwitterFormat(Date dt) {
		return getDateInFormat(dt, "yyyy-MM-dd");
	}
	
	// does this  need to use 24 hours?!?
	private static String getDateInMySQLFormat(Date dt) {
		return getDateInFormat(dt, "yyyy-MM-dd HH:mm:ss");
	}
	
	private static String getDateInFormat(Date dt, String format) {
		
		SimpleDateFormat formatter = new SimpleDateFormat(format, 
				Locale.getDefault());
		
		// DO I WANT TO HAVE THIS IN HERE?!?
		formatter.setTimeZone(TimeZone.getTimeZone("GMT"));
		
		return formatter.format(dt);
	}

}
